﻿using System;
using System.Data;
using System.Configuration;
using System.Reflection;
using System.Collections;
using System.Xml;
using System.Data.SqlClient;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Web;


/// <summary>
/// Summary description for SPCalling
/// </summary>
public class SPCalling
{
    private static Type objTypeCon = null; 
    private IDbConnection objCon = null; 
    private static Type objTypeCmd = null;
    private IDbTransaction objTrans;
    private bool isTransactionActive;
    private static Type objTypeAdapter = null;
	public SPCalling()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    #region ExecuteDataset
    public DataSet ExecuteDataset(CommandType commandType, string commandText)
    {
        DataSet objDS = new DataSet();
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, (IDbDataParameter[])null);
        IDbDataAdapter objDA = CreateDataAdapter();
        objDA.SelectCommand = cmd;
        objDA.Fill(objDS);
        cmd.Parameters.Clear();
        connection.Close();
        return objDS;
    }
    public DataSet ExecuteDataset(CommandType commandType, string commandText, params object[] commandParameters)
    {
        DataSet objDS = new DataSet();
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        //Shalin - Increased the commandtimeout to 120 secs to avoid query timeout error//
        //cmd.CommandTimeout=120;
        //Meenakshi - Increased the commandtimeout to 300 secs to avoid query timeout error//
        //cmd.CommandTimeout=300;
        //Rajeswaran - Increased the commandtimeout to 600 secs to avoid query timeout error//
        cmd.CommandTimeout = 600;
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        IDbDataAdapter objDA = CreateDataAdapter();
        objDA.SelectCommand = cmd;
        objDA.Fill(objDS);
        cmd.Parameters.Clear();
        connection.Close();
        return objDS;
    }
    public DataSet ExecuteDataset(CommandType commandType, string commandText, bool blnCloseConnection, params object[] commandParameters)
    {
        DataSet objDS = new DataSet();
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        //Shalin - Increased the commandtimeout to 120 secs to avoid query timeout error//
        //cmd.CommandTimeout=120;
        //Meenakshi - Increased the commandtimeout to 300 secs to avoid query timeout error//
        //cmd.CommandTimeout=300;
        //Rajeswaran - Increased the commandtimeout to 600 secs to avoid query timeout error//
        cmd.CommandTimeout = 600;
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        IDbDataAdapter objDA = CreateDataAdapter();
        objDA.SelectCommand = cmd;
        objDA.Fill(objDS);
        cmd.Parameters.Clear();
        if (blnCloseConnection)
            connection.Close();
        return objDS;
    } 
    #endregion ExecuteDataset
    #region ExecuteScalar
    public void ExecuteScalar(CommandType commandType, string commandText)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, (IDbDataParameter[])null);
        object retval = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        connection.Close();
        

    }
    public void ExecuteScalar(CommandType commandType, string commandText, params object[] commandParameters)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        object retval = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        connection.Close();
        
    }
    public void ExecuteScalar(CommandType commandType, string commandText, bool blnCloseConnection, params object[] commandParameters)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        object retval = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        if (blnCloseConnection)
            connection.Close();
        
    }
    #endregion ExecuteScalar
    #region ExecuteNonQuery
    public int ExecuteNonQuery(CommandType commandType, string commandText)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, (IDbDataParameter[])null);
        int retval = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        connection.Close();
        return retval;
    } 		
    public int ExecuteNonQuery(CommandType commandType, string commandText, params object[] commandParameters)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        int retval = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        connection.Close();
        return retval;
    }
    public int ExecuteNonQuery(CommandType commandType, string commandText, bool blnCloseConnection, params object[] commandParameters)
    {
        IDbConnection connection = GetConnection();
        IDbCommand cmd = CreateCommand();
        PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters);
        int retval = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        if (blnCloseConnection)
            connection.Close();
        return retval;
    }
    #endregion ExecuteNonQuery
    private IDbConnection GetConnection()
    {

        if (objCon == null)
        {
            objCon = CreateConnection();
            string strcon = "";
            strcon = ConfigurationSettings.AppSettings["CONN_STR"].ToString();
            objCon.ConnectionString = strcon;
        }

        if (objCon.State != ConnectionState.Open)
        {
            objCon.Open();
        }


        return objCon;
    }

    public static IDbCommand CreateCommand()
    {

        if (objTypeCmd == null)
        {
            Assembly objAssembly;
            objAssembly = Assembly.GetExecutingAssembly();
            AssemblyName[] objName = objAssembly.GetReferencedAssemblies();
            foreach (AssemblyName obj in objName)
            {
                if (obj.Name == ConfigurationManager.AppSettings["assembly"])
                {
                    Assembly objAssemblyName;
                    objAssemblyName = Assembly.Load(obj.FullName);
                    objTypeCmd = objAssemblyName.GetType(ConfigurationManager.AppSettings["command"]);
                    break;
                }
            }
        }
        return (IDbCommand)Activator.CreateInstance(objTypeCmd);
    }
    private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, object[] commandParameters)
    {

        command.Connection = connection;
        command.CommandText = commandText;
        if (objTrans != null)
        {
            command.Transaction = objTrans;
        }
        command.CommandType = commandType;

        if (!isTransactionActive)
            SqlCommandBuilder.DeriveParameters((SqlCommand)command);

        if (commandParameters != null)
        {
            if (!isTransactionActive)
                AttachCommandParameters((SqlCommand)command, commandParameters);
            else
                AttachParameters(command, commandParameters);
        }
        return;
    }

    private void AttachCommandParameters(SqlCommand command, object[] objParamValues)
    {

        for (int i = 1; i < command.Parameters.Count; i++)
        {
            SqlParameter objParam = (SqlParameter)command.Parameters[i];

            if ((objParam.Direction == ParameterDirection.InputOutput || objParam.Direction == ParameterDirection.Output) && (objParam.Value == null))
            {
                objParam.Value = DBNull.Value;
            }
            objParam.Value = objParamValues[i - 1];
        }
    }

    private void AttachParameters(IDbCommand command, object[] commandParameters)
    {

        for (int i = 0; i < commandParameters.Length; i++)
        {
            IDataParameter objParam = (IDataParameter)commandParameters[i];

            if ((objParam.Direction == ParameterDirection.InputOutput || objParam.Direction == ParameterDirection.Output) && (objParam.Value == null))
            {
                objParam.Value = DBNull.Value;
            }

            command.Parameters.Add(objParam);
        }
    }
    public static IDbDataAdapter CreateDataAdapter()
    {

        if (objTypeAdapter == null)
        {
            Assembly objAssembly;
            objAssembly = Assembly.GetExecutingAssembly();
            AssemblyName[] objName = objAssembly.GetReferencedAssemblies();
            foreach (AssemblyName obj in objName)
            {
                if (obj.Name == ConfigurationManager.AppSettings["assembly"])
                {
                    Assembly objAssemblyName;
                    objAssemblyName = Assembly.Load(obj.FullName);
                    objTypeAdapter = objAssemblyName.GetType(ConfigurationManager.AppSettings["dataadapter"]);
                    break;
                }
            }

        }
        return (IDbDataAdapter)Activator.CreateInstance(objTypeAdapter);
    }


    public static IDbConnection CreateConnection()
    {

        if (objTypeCon == null)
        {
            Assembly objAssembly;
            objAssembly = Assembly.GetExecutingAssembly();
            AssemblyName[] objName = objAssembly.GetReferencedAssemblies();
            foreach (AssemblyName obj in objName)
            {
                if (obj.Name == ConfigurationManager.AppSettings["assembly"])
                {
                    Assembly objAssemblyName;
                    objAssemblyName = Assembly.Load(obj.FullName);
                    objTypeCon = objAssemblyName.GetType(ConfigurationManager.AppSettings["connection"]);
                    break;

                }

            }

        }
        return (IDbConnection)Activator.CreateInstance(objTypeCon);
    }
    public void BeginTransaction()
    {
        objCon = GetConnection();
        objTrans = objCon.BeginTransaction(IsolationLevel.ReadCommitted);
        isTransactionActive = true;
    }
    public void CommitTransaction()
    {
        if (isTransactionActive)
        {
            objTrans.Commit();
            isTransactionActive = false;
            objTrans = null;
        }
        CloseConnection();
    }
    public void RollbackTransaction()
    {
        if (isTransactionActive)
        {
            objTrans.Rollback();
            isTransactionActive = false;
            objTrans = null;
        }
        CloseConnection();

    }
    public void CloseConnection()
    {
        if (objCon != null)
        {
            if (objCon.State == ConnectionState.Open)
            {
                objCon.Close();
            }
        }
    }

}